<?php
/*
Template Name: So You... Guide Layout
*/
?>

<?php get_header(); ?>
<?php global $woo_options; ?>

<style>
    .yarpp-related { display:none; }
    ul.listproductlist { list-style-type:none;padding:0px;margin:0px; }
        ul.listproductlist li { list-style-type:none;padding-left:0px; }
        ul.listproductlist h3 { padding-bottom:0px;margin-bottom:5px; }
        ul.listproductlist p { font-size:11px; }
        ul.listproductlist p.price del, .guidefeaturedproducts p.price del { display:none; }
        ul.listproductlist p.price ins, .guidefeaturedproducts p.price ins { text-decoration:none; }
        ul.listproductlist p.price span.amount, .guidefeaturedproducts p.price span.amount { font-size:20px;font-weight:bold;color:#3a3f55; }
</style>
       
    <div id="content" class="page col-full">
		<div id="main" class="col-left">
		           
		<?php if ( $woo_options[ 'woo_breadcrumbs_show' ] == 'true' ) { ?>
			<?php woo_breadcrumbs(); ?>  
		<?php } ?>  			

        <?php if (have_posts()) : $count = 0; ?>
        <?php while (have_posts()) : the_post(); $count++; ?>
                                                                    
            <div <?php post_class(); ?>>

			    <h1 class="title"><?php the_title(); ?></h1>
                            <p style="font-weight:bold;font-style:italic;color:#666666;">List Submitted By:
                                <?php if(get_field('listsubmittedby')) { 
                                    the_field('guidesubmittedby');
                                } else {
                                    echo 'Matthew Brown';
                                } ?>
                            </p>
                            <?php include '/home/silicons/public_html/wp-content/themes/woostore/page-include-social-block.php'; ?>

                <div class="entry">
                    <div class="guidefeaturedproducts">
                    <!-- INSERT THE FIRST THREE PRODUCTS AS FEATURED PRODUCTS -->
                    <?php while(has_sub_field('guideproducts')) : ?>
                    
                        <?php $products = get_field('guideproducts');
                            $firstproduct = $products[0];
                                $firstproductid = $firstproduct['guideproductid'];
                            $secondproduct = $products[1];
                                $secondproductid = $secondproduct['guideproductid'];
                            $thirdproduct = $products[2];
                                $thirdproductid = $thirdproduct['guideproductid'];
                        endwhile; ?>
                            
                            <?php // BEGIN PRODUCT LOOP - First Product
                                
                                $guideproductone = new wp_query('post_type=product&posts_per_page=3&p='.$firstproductid.'');
                                    while($guideproductone->have_posts()) : $guideproductone->the_post();
                                    
                                    // Get The Product Image
                                    if (get_field('productFeaturedImage') != '') {
                                        $guideproductimage = get_field('productFeaturedImage');
                                    } else {
                                        $guideproductimage = wp_get_attachment_image_src( get_post_thumbnail_id( $post->ID ), 'large');
                                        $guideproductimage = $guideproductimage[0];
                                    }
                                                                                
                                    // Get The Star Rating
                                    $count = $wpdb->get_var( $wpdb->prepare("
                                        SELECT COUNT(meta_value) FROM $wpdb->commentmeta
                                        LEFT JOIN $wpdb->comments ON $wpdb->commentmeta.comment_id = $wpdb->comments.comment_ID
                                        WHERE meta_key = 'rating'
                                        AND comment_post_ID = %d
                                        AND comment_approved = '1'
                                        AND meta_value > 0
                                    ", $post->ID ) );

                                    $rating = $wpdb->get_var( $wpdb->prepare("
                                        SELECT SUM(meta_value) FROM $wpdb->commentmeta
                                        LEFT JOIN $wpdb->comments ON $wpdb->commentmeta.comment_id = $wpdb->comments.comment_ID
                                        WHERE meta_key = 'rating'
                                        AND comment_post_ID = %d
                                        AND comment_approved = '1'
                                    ", $post->ID ) );

                                    $average = number_format($rating / $count, 2);
                            ?>
                    
                                <div style="width: 180px; padding: 10px; float: left; margin: 0px; text-align: center;">
                                    <a href="<?php the_permalink(); ?>">
                                        <img src="<?php echo $guideproductimage; ?>" alt="<?php the_title(); ?>" style="width:168px;height:168px;margin-bottom:10px;" />
                                    </a>
                                    <h3><a href="<?php the_permalink(); ?>"><?php the_title(); ?></a></h3>
                                    <?php echo '<div class="star-rating" style="margin-bottom:10px;" title="'.sprintf(__( 'Rated %s out of 5', 'woocommerce' ), $average).'"><span style="width:'.( ( $average / 5 ) * 100 ) . '%"><strong itemprop="ratingValue" class="rating">'.$average.'</strong> '.__( 'out of 5', 'woocommerce' ).'</span></div>'; ?>
                                    <?php include '/home/silicons/public_html/wp-content/themes/woostore/woocommerce/single-product/price.php'; ?>
                                    <p><a href="<?php the_permalink(); ?>" class="button">View &amp; Buy Now</a></p>
                                </div>
                    
                            <?php endwhile; ?>
                            <?php wp_reset_query(); ?>
                    
                            <?php // BEGIN PRODUCT LOOP - Second Product
                                
                                $guideproducttwo = new wp_query('post_type=product&posts_per_page=1&p='.$secondproductid.'');
                                    while($guideproducttwo->have_posts()) : $guideproducttwo->the_post();
                                    
                                    // Get The Product Image
                                    if (get_field('productFeaturedImage') != '') {
                                        $guideproductimage = get_field('productFeaturedImage');
                                    } else {
                                        $guideproductimage = wp_get_attachment_image_src( get_post_thumbnail_id( $post->ID ), 'large');
                                        $guideproductimage = $guideproductimage[0];
                                    }
                                                                                
                                    // Get The Star Rating
                                    $count = $wpdb->get_var( $wpdb->prepare("
                                        SELECT COUNT(meta_value) FROM $wpdb->commentmeta
                                        LEFT JOIN $wpdb->comments ON $wpdb->commentmeta.comment_id = $wpdb->comments.comment_ID
                                        WHERE meta_key = 'rating'
                                        AND comment_post_ID = %d
                                        AND comment_approved = '1'
                                        AND meta_value > 0
                                    ", $post->ID ) );

                                    $rating = $wpdb->get_var( $wpdb->prepare("
                                        SELECT SUM(meta_value) FROM $wpdb->commentmeta
                                        LEFT JOIN $wpdb->comments ON $wpdb->commentmeta.comment_id = $wpdb->comments.comment_ID
                                        WHERE meta_key = 'rating'
                                        AND comment_post_ID = %d
                                        AND comment_approved = '1'
                                    ", $post->ID ) );

                                    $average = number_format($rating / $count, 2);
                            ?>
                    
                                <div style="width: 180px; padding: 10px; float: left; margin: 0px; text-align: center;">
                                    <a href="<?php the_permalink(); ?>">
                                        <img src="<?php echo $guideproductimage; ?>" alt="<?php the_title(); ?>" style="width:168px;height:168px;margin-bottom:10px;" />
                                    </a>
                                    <h3><a href="<?php the_permalink(); ?>"><?php the_title(); ?></a></h3>
                                    <?php echo '<div class="star-rating" style="margin-bottom:10px;" title="'.sprintf(__( 'Rated %s out of 5', 'woocommerce' ), $average).'"><span style="width:'.( ( $average / 5 ) * 100 ) . '%"><strong itemprop="ratingValue" class="rating">'.$average.'</strong> '.__( 'out of 5', 'woocommerce' ).'</span></div>'; ?>
                                    <?php include '/home/silicons/public_html/wp-content/themes/woostore/woocommerce/single-product/price.php'; ?>
                                    <p><a href="<?php the_permalink(); ?>" class="button">View &amp; Buy Now</a></p>
                                </div>
                    
                            <?php endwhile; ?>
                            <?php wp_reset_query(); ?>
                    
                            <?php // BEGIN PRODUCT LOOP - Third Product
                                
                                $guideproductthree = new wp_query('post_type=product&posts_per_page=1&p='.$thirdproductid.'');
                                    while($guideproductthree->have_posts()) : $guideproductthree->the_post();
                                    
                                    // Get The Product Image
                                    if (get_field('productFeaturedImage') != '') {
                                        $guideproductimage = get_field('productFeaturedImage');
                                    } else {
                                        $guideproductimage = wp_get_attachment_image_src( get_post_thumbnail_id( $post->ID ), 'large');
                                        $guideproductimage = $guideproductimage[0];
                                    }
                                                                                
                                    // Get The Star Rating
                                    $count = $wpdb->get_var( $wpdb->prepare("
                                        SELECT COUNT(meta_value) FROM $wpdb->commentmeta
                                        LEFT JOIN $wpdb->comments ON $wpdb->commentmeta.comment_id = $wpdb->comments.comment_ID
                                        WHERE meta_key = 'rating'
                                        AND comment_post_ID = %d
                                        AND comment_approved = '1'
                                        AND meta_value > 0
                                    ", $post->ID ) );

                                    $rating = $wpdb->get_var( $wpdb->prepare("
                                        SELECT SUM(meta_value) FROM $wpdb->commentmeta
                                        LEFT JOIN $wpdb->comments ON $wpdb->commentmeta.comment_id = $wpdb->comments.comment_ID
                                        WHERE meta_key = 'rating'
                                        AND comment_post_ID = %d
                                        AND comment_approved = '1'
                                    ", $post->ID ) );

                                    $average = number_format($rating / $count, 2);
                            ?>
                    
                                <div style="width: 180px; padding: 10px; float: left; margin: 0px; text-align: center;">
                                    <a href="<?php the_permalink(); ?>">
                                        <img src="<?php echo $guideproductimage; ?>" alt="<?php the_title(); ?>" style="width:168px;height:168px;margin-bottom:10px;" />
                                    </a>
                                    <h3><a href="<?php the_permalink(); ?>"><?php the_title(); ?></a></h3>
                                    <?php echo '<div class="star-rating" style="margin-bottom:10px;" title="'.sprintf(__( 'Rated %s out of 5', 'woocommerce' ), $average).'"><span style="width:'.( ( $average / 5 ) * 100 ) . '%"><strong itemprop="ratingValue" class="rating">'.$average.'</strong> '.__( 'out of 5', 'woocommerce' ).'</span></div>'; ?>
                                    <?php include '/home/silicons/public_html/wp-content/themes/woostore/woocommerce/single-product/price.php'; ?>
                                    <p><a href="<?php the_permalink(); ?>" class="button">View &amp; Buy Now</a></p>
                                </div>
                    
                            <?php endwhile; ?>
                            <?php wp_reset_query(); ?>
                        
                    </div>
                    <!-- END FEATURED PRODUCT SECTION AND BEGIN THE GUIDE CONTENT -->

                    <div style="width:100%;height:0px;clear:both;"></div>
                    
                    <?php the_content(); ?>

                    <h2 class="pagesubsectionheader">Solar Products Used In This Guide</h2>

                    <ul class="listproductlist">

						<?php // Get The List Of Product IDs
                                                    while(has_sub_field('guideproducts')) {
                                                                $productrank = get_sub_field('guideproductrank');
								$productid = get_sub_field('guideproductid');
								$producttext = get_sub_field('guideproducttext');
						?>

							<?php $guideproducts = new wp_query('post_type=product&posts_per_page=5&p='.$productid.'');
									while($guideproducts->have_posts()) : $guideproducts->the_post();

										// Get The Product Image
										if (get_field('productFeaturedImage') != '') {
											$guideproductimage = get_field('productFeaturedImage');
										} else {
											$guideproductimage = wp_get_attachment_image_src( get_post_thumbnail_id( $post->ID ), 'large');
											$guideproductimage = $guideproductimage[0];
										}
                                                                                
                                                                                // Get The Star Rating
                                                                                $count = $wpdb->get_var( $wpdb->prepare("
			SELECT COUNT(meta_value) FROM $wpdb->commentmeta
			LEFT JOIN $wpdb->comments ON $wpdb->commentmeta.comment_id = $wpdb->comments.comment_ID
			WHERE meta_key = 'rating'
			AND comment_post_ID = %d
			AND comment_approved = '1'
			AND meta_value > 0
		", $post->ID ) );

		$rating = $wpdb->get_var( $wpdb->prepare("
			SELECT SUM(meta_value) FROM $wpdb->commentmeta
			LEFT JOIN $wpdb->comments ON $wpdb->commentmeta.comment_id = $wpdb->comments.comment_ID
			WHERE meta_key = 'rating'
			AND comment_post_ID = %d
			AND comment_approved = '1'
		", $post->ID ) );

		$average = number_format($rating / $count, 2);
            ?>

								<li style="padding-top:10px;padding-bottom:10px;border-bottom:#cccccc 1px solid;">
                                                                    <div style="width:112px;height:112px;padding-right:10px;float:left;margin:0px;">
                                                                        <a href="<?php the_permalink(); ?>">
                                                                            <img src="<?php echo $guideproductimage; ?>" alt="<?php the_title(); ?>" style="width:100px;height:100px;" />
                                                                        </a>
                                                                    </div>
                                                                    <div style="width:330px;height:112px;float:left;margin:0px;">
                                                                        <h3><a href="<?php the_permalink(); ?>"><?php the_title(); ?></a></h3>
                                                                        <p><?php echo $producttext; ?></p>
                                                                    </div>
                                                                    <div style="width:158px;height:112px;padding-left:10px;float:right;margin:0px;text-align:right;">
                                                                        <?php include '/home/silicons/public_html/wp-content/themes/woostore/woocommerce/single-product/price.php'; ?>
                                                                        <?php
                                                                            echo '<div class="star-rating" style="margin-bottom:10px;" title="'.sprintf(__( 'Rated %s out of 5', 'woocommerce' ), $average).'"><span style="width:'.( ( $average / 5 ) * 100 ) . '%"><strong itemprop="ratingValue" class="rating">'.$average.'</strong> '.__( 'out of 5', 'woocommerce' ).'</span></div>';
                                                                        ?>
                                                                        <p><a href="<?php the_permalink(); ?>" class="button">View &amp; Buy Today</a></p>
                                                                    </div>
                                                                    <div style="width:100%;height:0px;clear:both;"></div>
								</li>

							<?php endwhile; ?><!-- $guideproducts -->
							<?php wp_reset_query(); ?>

                                                        <?php } ?><!-- guideproductids -->

					</ul>

               	</div><!-- /.entry -->

				<?php edit_post_link( __( '{ Edit }', 'woothemes' ), '<span class="small">', '</span>' ); ?>
                
            </div><!-- /.post -->
                                                
		<?php endwhile; else: ?>
			<div <?php post_class(); ?>>
            	<p><?php _e( 'Sorry, no posts matched your criteria.', 'woothemes' ) ?></p>
            </div><!-- /.post -->
        <?php endif; ?>  
        
		</div><!-- /#main -->

        <?php get_sidebar(); ?>

    </div><!-- /#content -->
		
<?php get_footer(); ?>